package edu.jhu.secondhome.database.accessors;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jhu.secondhome.database.ConnectionPool;
import edu.jhu.secondhome.database.DataUtil;
import edu.jhu.secondhome.database.beans.Hotel;

public class HotelAccessor extends Accessor {

  /**
   * Gets all hotels from the database
   * @return List<Hotel>
   * @throws SQLException
   */
  public List<Hotel> getHotels() throws SQLException {
    List<Hotel> hotels = new ArrayList<Hotel>();

    final ConnectionPool pool = ConnectionPool.getInstance();
    final Connection conn = pool.getConnection();
    PreparedStatement ps = null;

    try {
      ps = conn.prepareStatement("SELECT * FROM Hotels");
      ResultSet rs = ps.executeQuery();
      // Move to first result:
      rs.next();
      // Process results
      while (!rs.isAfterLast()) {
        hotels.add(extractHotel(rs));
        rs.next();
      }
    } finally {
      DataUtil.closeStatement(ps);
      pool.freeConnection(conn);
    }
    return hotels;
  }

  /* Returns a hotel from the DB using name as 
   * the key. 
   * @param name - the hotel name
   * 
   * Throws SQL Exception
   */
  public Hotel getHotelByName(String name) throws SQLException
  {
    Hotel hotel = new Hotel();
    final ConnectionPool pool = ConnectionPool.getInstance();
    final Connection conn = pool.getConnection();
    PreparedStatement ps = null;

    try {
      ps = conn.prepareStatement(String.format("SELECT * FROM Hotels WHERE hotelName='%s'", name));
      ResultSet rs = ps.executeQuery();
      // Move to first result:
      rs.next();
      // Process results (We used the primary key so there will only be one result
      hotel = extractHotel(rs);
    } finally {
      DataUtil.closeStatement(ps);
      pool.freeConnection(conn);
    }
    return hotel;
  }

  /**
   * Inserts a new hotel into the database
   * @param hotel - bean filled with the information to add
   * @return int (from PreparedStatement: the row count for SQL Data 
   * 			Manipulation Language (DML) statements or 0 for statements 
   * 			that return nothing)
   * @throws SQLException
   */
  public int insertHotel(Hotel hotel) throws SQLException {
    String sql = "INSERT INTO Hotels (hotelName, description, " +
        "address, phoneNumber, amenities, thumbnail" + 
        ") VALUES (?, ?, ?, ?, ?)";

    String[] params = {
        hotel.getHotelName(),
        hotel.getDescription(),
        hotel.getAddress(),
        hotel.getPhoneNumber(),
        hotel.getAmenities(),
        hotel.getThumbnail()
    };

    return executeUpdateStatement(sql, params);
  }

  /**
   * Updates the hotel information.
   * @param hotel - bean filled with information to update the hotel
   * @return int (from PreparedStatement: the row count for SQL Data 
   * 			Manipulation Language (DML) statements or 0 for statements 
   * 			that return nothing)
   * @throws SQLException
   */
  public int updateHotel(Hotel hotel) throws SQLException {
    String sql = "UPDATE Hotels SET " +
        "description = ?, address = ?, " +
        "phoneNumber = ?, amenities = ? " + 
        "thumbnail = ? " + 
        "WHERE hotelName = ?";

    String[] params = {
        hotel.getDescription(),
        hotel.getAddress(),
        hotel.getPhoneNumber(),
        hotel.getAmenities(), 
        hotel.getThumbnail(), 
        hotel.getHotelName()
    };

    return executeUpdateStatement(sql, params);
  }



  /**
   * Deletes a hotel from the database
   * @param hotel - bean with the hotelName of the hotel to delete
   * @return int (from PreparedStatement: the row count for SQL Data 
   * 			Manipulation Language (DML) statements or 0 for statements 
   * 			that return nothing)
   * @throws SQLException
   */
  public int deleteHotel(Hotel hotel) throws SQLException {
    String sql = "DELETE FROM Hotels " + 
        "WHERE hotelName = ?";

    String[] params = { hotel.getHotelName() };

    return executeUpdateStatement(sql, params);
  }

  /**
   * Deletes a hotel from the database
   * @param hotelName - name of the hotel to delete
   * @return int (from PreparedStatement: the row count for SQL Data 
   * 			Manipulation Language (DML) statements or 0 for statements 
   * 			that return nothing)
   * @throws SQLException
   */
  public int deleteHotel(final String hotelName) throws SQLException {
    String sql = "DELETE FROM Hotels " + 
        "WHERE hotelName = ?";

    String[] params = { hotelName };

    return executeUpdateStatement(sql, params);
  }

  //      SELECT * FROM Hotels WHERE hotelName IN (SELECT distinct hotelName FROM Rooms WHERE occupancy >= '2' AND hotelName NOT IN (SELECT hotelName FROM Reservations WHERE ((startDate BETWEEN '2015-01-17' AND DATE_ADD('2015-01-17', INTERVAL '3' DAY)) OR (DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) BETWEEN '2015-01-17' AND DATE_ADD('2015-01-17', INTERVAL '3' DAY )))));
  public List<Hotel> getAvailableHotelsForDateRangeAndOccupancy( Date start, int numDays, int occupancy ) throws SQLException {
    List<Hotel> hotels = new ArrayList<Hotel>();
    final ConnectionPool pool = ConnectionPool.getInstance();
    final Connection conn = pool.getConnection();
    PreparedStatement ps = null;
    try {
      ps = conn.prepareStatement( "SELECT * FROM Hotels WHERE hotelName IN (" +
          "SELECT distinct hotelName " +
          "FROM Rooms " + 
          "WHERE occupancy >= ? " +
          "AND roomNumber NOT IN (" +
          "SELECT roomNumber FROM Reservations WHERE (" +
            "(startDate BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY)) OR " +
            "(DATE_ADD(startDate, INTERVAL (numOfDays-1) DAY) BETWEEN ? AND DATE_ADD(?, INTERVAL ? DAY )))))");

      ps.setInt( 1, occupancy );
      ps.setDate( 2, start );
      ps.setDate( 3, start );
      ps.setInt( 4, numDays - 1 );
      ps.setDate( 5, start );
      ps.setDate( 6, start );
      ps.setInt( 7, numDays - 1 );

      ResultSet rs = ps.executeQuery();
      // Move to first result: 
      boolean available = rs.next();
      // Process results
      if ( available )
      {
        while (!rs.isAfterLast())
        {
          hotels.add(extractHotel(rs));
          rs.next();
        }
      }
    } finally {
      DataUtil.closeStatement(ps);
      pool.freeConnection(conn);
    }

    return hotels;
  }	

  /**
   * Helper method to extract the hotel information from the ResultSet 
   * row and put it into the bean
   * @param rs - ResultSet pointing to the row of data to pull from
   * @return populated Hotel object
   * @throws SQLException
   */
  private Hotel extractHotel(ResultSet rs) throws SQLException {
    return new Hotel(rs.getString("hotelName"), rs.getString("description"), 
        rs.getString("address"), rs.getString("phoneNumber"), 
        rs.getString("amenities"), rs.getString( "thumbnail" ));
  }
}
